# 业务数据库 -> ODS -> DWD -> DWM -> DWS -> MySQL -> BI
# 针对ODS层建库建表开发操作、建模

HIVE_HOME=/usr/bin/hive
SQOOP_HOME=/usr/bin/sqoop

# 需求：能够手动传入参数指定采集的日期。如果不传入参数要求能够自动的识别前一天的日期
if [ $# -eq 0 ]
then
    DATESTR=`date -d "-1 day" +%Y-%m-%d`
else
    DATESTR=$1
fi

${HIVE_HOME} -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
--插入数据
INSERT INTO itcast_dwd.itcast_clue_dwd PARTITION (yearinfo, monthinfo, dayinfo)
SELECT
    clue.id,
    clue.customer_relationship_id,
    if(rs.origin_type='NETSERVICE' or rs.origin_type='PRESIGNUP', '1', '0') as origin_type_stat,
    if(clue.clue_state='VALID_NEW_CLUES', 1, if(clue.clue_state='VALID_PUBLIC_NEW_CLUE', 2, 0)) as for_new_user,
    clue.deleted,
    clue.create_date_time,
    substr(clue.create_date_time, 12, 2) as hourinfo,
    substr(clue.create_date_time, 1, 4) as yearinfo,
    substr(clue.create_date_time, 6, 2) as monthinfo,
    substr(clue.create_date_time, 9, 2) as dayinfo
FROM itcast_ods.customer_clue clue
LEFT JOIN itcast_ods.customer_relationship rs on clue.customer_relationship_id = rs.id
where clue.clue_state is not null AND clue.deleted = 'false' AND AND start_time = '${DATESTR}';--2020-01-05
"
